package EBM.DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import EBM.BLL.EBMConstant;
import EBM.model.Bug;
import EBM.model.BugUser;

public class BugDAO implements EBMConstant{
	private List<Object> response;
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	private int result;

	public BugDAO(){
		response = null;
		conn = null;
		ps = null;
		rs = null;
		result = 0;
	}

	public boolean createBugTable(Object proid)
	throws SQLException, ClassNotFoundException{

		conn = DBConnection.getConn();

		// Create bug table
		String sql ="CREATE TABLE bug_" + proid + " ("+
		//创建缺陷
		"`id` int(10) unsigned NOT NULL auto_increment,"+
		"`submit_time` datetime,"+
		"`current_step_time` datetime,"+
		"`bug_model` varchar(45) default NULL,"+
		"`hard_environment` varchar(200) default NULL,"+
		"`soft_environment` varchar(200) default NULL,"+
		"`serious_level` int(10) unsigned NOT NULL default '0',"+
		"`priority_level` int(10) unsigned NOT NULL default '0',"+
		"`simple_description` varchar(200) default NULL,"+
		"`detail_description` text,"+
		"`reappear` int(10) unsigned NOT NULL default '0',"+
		"`reappear_step` text,"+
		"`bug_appdix` text,"+
		//审核
		"`check_info` varchar(200) default NULL,"+
		//定位缺陷
		"`bug_source` varchar(200) default NULL,"+
		"`fix_info` varchar(200) default NULL,"+
		//修改缺陷
		"`fix_description` varchar(200) default NULL,"+
		"`fix_appdix` varchar(200) default NULL,"+
		//验证缺陷
		"`verify_pass` int(10) unsigned NOT NULL default '0',"+
		//其他信息
		"`bug_statu` int(10) unsigned NOT NULL default '0',"+

		"PRIMARY KEY  (`id`)"+
		") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

		ps = conn.prepareStatement(sql);
		try{
			ps.executeUpdate();
		}catch(SQLException ex){
			return false;
		}

		//Create bug_log table
		sql = "CREATE TABLE bug_log_" + proid + " ("+
		"`id` int(10) unsigned NOT NULL auto_increment,"+
		"`time` datetime default NULL,"+
		"`person` varchar(20) default NULL,"+
		"`detail` varchar(45) default NULL,"+
		"`bugid` int(10) unsigned default NULL,"+
		"PRIMARY KEY  (`id`),"+
		"KEY `FK_bug_log_bugid` (`bugid`),"+
		"CONSTRAINT FK_bug_log_bugid_" + proid + " FOREIGN KEY (`bugid`) REFERENCES bug_" + proid + " (`id`)"+
		") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

		ps = conn.prepareStatement(sql);
		try{
			ps.executeUpdate();
		}catch(SQLException ex){
			return false;
		}

		//Create bug_user table
		sql = "CREATE TABLE bug_user_" + proid + " ("+
		"`bugid` int(10) unsigned NOT NULL,"+
		"`userid` int(10) unsigned NOT NULL,"+
		"`user_type` int(10) unsigned default NULL,"+
		"PRIMARY KEY  (`bugid`,`userid`,`user_type`),"+
		"KEY `FK_bug_user_userid` (`userid`),"+
		"CONSTRAINT FK_bug_user_bugid_" + proid + " FOREIGN KEY (`bugid`) REFERENCES bug_" + proid + " (`id`),"+
		"CONSTRAINT FK_bug_user_userid_" + proid + " FOREIGN KEY (`userid`) REFERENCES `userinfo` (`id`)"+
		") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

		ps = conn.prepareStatement(sql);
		try{
			ps.executeUpdate();
		}catch(SQLException ex){
			return false;
		}

		conn.close();

		return true;
	}
	public void submitDraftBug(String projectName, int bugId)	throws SQLException, ClassNotFoundException{
		String sql = "update bug_"+ projectName + " set bug_statu = 1 where bug_statu = 0 and id = ?";
		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setInt(1, bugId);
		result = ps.executeUpdate();
		if (result > 0)
		{
			sql = "select userid from bug_user_" + projectName + " where bugid = ? and user_type = 2";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, bugId);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
			{
				int currentUser = rs.getInt(1);
				sql = "update bug_user_" + projectName + " set userid = ? where bugid = ? and user_type = 0";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, currentUser);
				ps.setInt(2, bugId);
				result = ps.executeUpdate();
			}
		}
	}
	public String save(Bug bug) throws SQLException, ClassNotFoundException{

		String sql="insert into bug_"+ bug.getProid() +
		"(submit_time,current_step_time,bug_model,hard_environment,soft_environment,serious_level,simple_description,detail_description,reappear,reappear_step,bug_appdix,bug_statu)"+
		" values(?,?,?,?,?,?,?,?,?,?,?,?)";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setObject(1, bug.getSubmit_time());
		ps.setObject(2, bug.getCurrent_step_time());
		ps.setObject(3, bug.getBug_model());
		ps.setObject(4, bug.getHard_environment());
		ps.setObject(5, bug.getSoft_environment());
		ps.setObject(6, bug.getSerious_level());
		ps.setObject(7, bug.getPriority_level());
		ps.setObject(7, bug.getSimple_description());
		ps.setObject(8, bug.getDetail_description());
		ps.setObject(9, bug.getReappear());
		ps.setObject(10, bug.getReappear_step());
		ps.setObject(11, bug.getBug_appdix());
		ps.setObject(12, bug.getBug_statu());

		response = new ArrayList<Object>();

		result = ps.executeUpdate();

		sql = "select * from bug_"+ bug.getProid() +" where submit_time=?";
		ps = conn.prepareStatement(sql);
		ps.setObject(1, bug.getSubmit_time());
		rs = ps.executeQuery();

		if(rs.next()){
			String id = rs.getString("id");
			conn.close();
			return id;
		}else{
			conn.close();
			return "-1";
		}	
	}

	public boolean update(Bug bug) throws SQLException, ClassNotFoundException{

		String sql="update bug_"+ bug.getProid() +
		" set submit_time=?,current_step_time=?,bug_model=?,hard_environment=?,soft_environment=?,serious_level=?,"+
		"priority_level=?,simple_description=?,detail_description=?,reappear=?,reappear_step=?,bug_appdix=?,bug_statu=?"+
		" where id=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setObject(1, bug.getSubmit_time());
		ps.setObject(2, bug.getCurrent_step_time());
		ps.setObject(3, bug.getBug_model());
		ps.setObject(4, bug.getHard_environment());
		ps.setObject(5, bug.getSoft_environment());
		ps.setObject(6, bug.getSerious_level());
		ps.setObject(7, bug.getPriority_level());
		ps.setObject(8, bug.getSimple_description());
		ps.setObject(9, bug.getDetail_description());
		ps.setObject(10, bug.getReappear());
		ps.setObject(11, bug.getReappear_step());
		ps.setObject(12, bug.getBug_appdix());
		ps.setObject(13, bug.getBug_statu());
		ps.setObject(14, bug.getId());

		result = ps.executeUpdate();
		conn.close();

		if(result>0){
			return true;
		}else{
			return false;
		}
	}

	public boolean updateBySQL(Object proid, Object set_clause, Object id) throws SQLException, ClassNotFoundException{

		String sql="update bug_"+ proid + " " + set_clause + " where id=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setObject(1, id);
		
		System.out.println("语句："+sql);
		
		result = ps.executeUpdate();
		conn.close();

		if(result>0){
			return true;
		}else{
			return false;
		}
	}
	
	public Bug findById(Object proid, Object id)
	throws SQLException, ClassNotFoundException{

		String sql="select * from bug_"+ proid + " where id=?";

		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setObject(1, id);

		rs = ps.executeQuery();

		if(rs.next()){	
			Bug bug = new Bug();
			bug.setProid(rs.getString("id"));
			bug.setSubmit_time(rs.getString("submit_time"));
			bug.setCurrent_step_time(rs.getString("current_step_time"));
			bug.setBug_model(rs.getString("bug_model"));		
			bug.setHard_environment(rs.getString("hard_environment"));
			bug.setSoft_environment(rs.getString("soft_environment"));
			bug.setSerious_level(rs.getString("serious_level"));
			bug.setPriority_level(rs.getString("priority_level"));
			bug.setSimple_description(rs.getString("simple_description"));
			bug.setDetail_description(rs.getString("detail_description"));
			bug.setReappear(rs.getString("reappear"));
			bug.setReappear_step(rs.getString("reappear_step"));
			bug.setBug_appdix(rs.getString("bug_appdix"));
			bug.setCheck_info(rs.getString("check_info"));
			bug.setBug_source(rs.getString("bug_source"));
			bug.setFix_info(rs.getString("fix_info")); 
			bug.setFix_description(rs.getString("fix_description"));
			bug.setFix_appdix(rs.getString("fix_appdix"));
			bug.setVerify_pass(rs.getString("verify_pass"));
			bug.setBug_statu(rs.getString("bug_statu"));
			conn.close();
			return bug;		
		}else{			
			return null;
		}
	}

	public List<Object> findByProid(Object proid)
	throws SQLException, ClassNotFoundException{

		String sql="select * from bug_"+proid+" order by current_step_time desc";
		
		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		
		//System.out.println("语句:"+sql);
		
		rs = ps.executeQuery();
		response = new ArrayList<Object>();
		if(rs.next()){	
			do{
				Bug b = new Bug();
				b.setId(rs.getString("bug_"+proid+".id"));
				b.setBug_statu(rs.getString("bug_"+proid+".bug_statu"));
				b.setSimple_description(rs.getString("bug_"+proid+".simple_description"));
				b.setCurrent_step_time(rs.getString("bug_"+proid+".current_step_time"));
				response.add(b);
			}while(rs.next());
			conn.close();			
		}
		
		return response;
	}
	
	public List<Object> findByUserid(Object proid, Object userid, Object user_type)
	throws SQLException, ClassNotFoundException{

		String sql="select distinct bug_"+proid+".id,bug_"+proid+".bug_statu,bug_"+proid+".simple_description,bug_"+proid+".current_step_time"+
		" from bug_"+ proid + ",bug_user_"+ proid +",userinfo"+
		" where bug_"+ proid + ".id=bug_user_"+ proid +".bugid"+
		" and bug_user_"+ proid + ".userid=userinfo.id"+
		" and bug_user_"+ proid + ".user_type=?"+
		" and userinfo.usrid=?";
		
		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);
		ps.setObject(1, user_type);
		ps.setObject(2, userid);
		
		//System.out.println("语句:"+sql);
		
		rs = ps.executeQuery();
		response = new ArrayList<Object>();
		if(rs.next()){	
			do{
				Bug b = new Bug();
				b.setId(rs.getString("bug_"+proid+".id"));
				b.setBug_statu(rs.getString("bug_"+proid+".bug_statu"));
				b.setSimple_description(rs.getString("bug_"+proid+".simple_description"));
				b.setCurrent_step_time(rs.getString("bug_"+proid+".current_step_time"));
				response.add(b);
			}while(rs.next());
			conn.close();			
		}
		
		return response;
	}
	
	public List<Object> findByCertainProperty(Object proid, Bug bug, BugUser bugUser)
	throws SQLException, ClassNotFoundException{

		String base="select distinct bug_"+proid+".id,bug_"+proid+".bug_statu,bug_"+proid+".simple_description,bug_"+proid+".current_step_time"+
		" from bug_"+ proid + ",bug_user_"+ proid +",userinfo"+
		" where bug_"+ proid + ".id=bug_user_"+ proid +".bugid"+
		" and bug_user_"+ proid + ".userid=userinfo.id";
		ArrayList<String> conditions = new ArrayList<String>();
		if (bug.getId().trim().equals("") == false)
			conditions.add(" bug_"+ proid +".id='"+bug.getId()+"' ");
		if (bug.getBug_statu().trim().equals("") == false)
			conditions.add(" bug_"+ proid +".bug_statu='"+bug.getBug_statu()+"'");
		if (bug.getBug_model().trim().equals("") == false)
			conditions.add(" bug_"+ proid +".bug_model='"+bug.getBug_model()+"'");
		if (bug.getSerious_level().trim().equals("") == false)
			conditions.add(" bug_"+ proid +".serious_level='"+bug.getSerious_level()+"'");
		if (bug.getStartTime().trim().equals("") == false)
			conditions.add(" bug_"+ proid +".submit_time>='"+bug.getStartTime()+"'");
		if (bug.getEndTime().trim().equals("") == false)
			conditions.add(" bug_"+ proid +".submit_time<='"+bug.getEndTime()+"'");
		for (int i = 0; i < conditions.size(); i++)
			base += " and" + conditions.get(i);
		
		String sql = base;
			
		if(!bugUser.getUsr_submit().trim().equals(""))
			sql += " and bug_"+ proid + ".id in (select bug_"+ proid +".id from bug_"+ proid + ",bug_user_"+ proid +",userinfo"+
			" where bug_"+ proid + ".id=bug_user_"+ proid +".bugid"+
			" and bug_user_"+ proid + ".userid=userinfo.id"+
			" and bug_user_"+ proid +".user_type="+usr_submit+
			" and userinfo.usrid='"+bugUser.getUsr_submit()+"')";
		
		if(!bugUser.getUsr_responsible().trim().equals("")){
			if(!bugUser.getUsr_submit().trim().equals(""))
				sql = sql + " union "+base;

			sql += " and bug_"+ proid + ".id in (select bug_"+ proid +".id from bug_"+ proid + ",bug_user_"+ proid +",userinfo"+
			" where bug_"+ proid + ".id=bug_user_"+ proid +".bugid"+
			" and bug_user_"+ proid + ".userid=userinfo.id"+
			" and bug_user_"+ proid +".user_type="+usr_responsible+
			" and userinfo.usrid='"+bugUser.getUsr_responsible()+"')";
		}
		System.out.println("语句:"+sql);
		conn = DBConnection.getConn();
		ps = conn.prepareStatement(sql);

		rs = ps.executeQuery();
		response = new ArrayList<Object>();
		if(rs.next()){	
			do{
				Bug b = new Bug();
				b.setId(rs.getString("bug_"+proid+".id"));
				b.setBug_statu(rs.getString("bug_"+proid+".bug_statu"));
				b.setSimple_description(rs.getString("bug_"+proid+".simple_description"));
				b.setCurrent_step_time(rs.getString("bug_"+proid+".current_step_time"));

				response.add(b);
			}while(rs.next());							
		}
		conn.close();	
		return response;
	}
	//Test method createBugTable
//	public static void main(String[] args) throws SQLException, ClassNotFoundException{
//	BugDAO d = new BugDAO();
//	d.createBugTable("10");
//	}

}
